package com.dqyt.khjc.controller;

import com.dqyt.app.bean.Report;
import com.dqyt.app.bean.Sample;
import com.dqyt.app.bean.WorkBean;
import com.dqyt.app.mongo.MaintenanceDao;
import com.dqyt.app.mongo.ReportDao;
import com.dqyt.app.mongo.SampleDao;
import com.dqyt.khjc.bean.ResultMsg;
import com.dqyt.khjc.bean.TzBean;
import com.dqyt.khjc.bean.TzMzdelBean;
import com.dqyt.khjc.bean.TzQpdelBean;
import com.dqyt.khjc.mapper.ReceiptTbMapper;
import com.dqyt.khjc.mapper.WorkMapper;
import com.dqyt.khjc.utils.TimeUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.data.mongodb.core.aggregation.ArrayOperators;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

//台账
@RestController
public class TzController {


    @Autowired
    private WorkMapper workMapper;
    @Autowired
    private ReportDao reportDao;
    @Autowired
    private MaintenanceDao maintenanceDao;
    @Autowired
    private SampleDao sampleDao;
    @Autowired
    private ReceiptTbMapper receiptTbMapper;



    /**
     * 台账统计的分页查询任务
     *
     * @param request
     * @return
     */
    @RequestMapping("/api/downloadTzData")
    public ResultMsg downloadTzData(HttpServletResponse response, HttpServletRequest request,
                                    @RequestParam(name = "startTime", required = false, defaultValue = "")  String startTime,
                                    @RequestParam(name = "endTime", required = false, defaultValue = "")  String endTime,
                                    @RequestParam(defaultValue = "-1") String wtfId,
                                    @RequestParam(defaultValue = "-1") String rwdbh,
                                    @RequestParam(defaultValue = "0") String wtfchId
    ) {
        ResultMsg resultMsg = new ResultMsg();

        try {
            long mstarttime = 0;
            long mendTime = 0;
            long mwtfId = 0;
            if (wtfId.equals(-1)) {
                mwtfId = 0;
            } else {
                mwtfId = Long.parseLong(wtfId);
            }

            if("".equals(startTime) ){
                mstarttime = 0;
            }else{
                mstarttime = TimeUtil.formatDate_5(startTime);
            }


            if("".equals(endTime)){
                mendTime = 0;
            }else{
                mendTime = TimeUtil.formatDate_5(endTime);
            }


            long mWtfchId =0;
            if("0".equals(wtfchId)){
                mWtfchId =0;
            }else{
                mWtfchId = Long.parseLong(wtfchId);
            }

            //查询的是work表
            List<WorkBean> workBeans = workMapper.getTzStatisticsPage2(rwdbh,mwtfId,mWtfchId, mstarttime, mendTime);

            //把所有的标识编号都取出来
            List<String> bsbhList = new ArrayList<>();
            for (WorkBean workBean : workBeans) {
                bsbhList.add(workBean.getBsbh());
            }
            //然后查询已完成的报告 面罩的个数
            List<Sample> mzNumbers = sampleDao.getReportByBatch(bsbhList, 1);
            //然后查询已完成的报告 气瓶的个数
            List<Sample> qpNumbers = sampleDao.getReportByBatch(bsbhList, 2);




            ClassPathResource cpr = new ClassPathResource("excleTemplate/taizhang.xlsx");
            InputStream is = cpr.getInputStream();
            Workbook workbook = new XSSFWorkbook(is);
            Sheet sheet0 = workbook.getSheetAt(0);
            //最后的提示信息
            String tipsMsg="";
            //先循环气瓶
            flag:
            for(int a=0;a<qpNumbers.size();a++){
                //气瓶标识编号
                String qpBsbh=qpNumbers.get(a).bsbh;
                //提示信息
                String tisMnBsbh="";
                //面罩匹配的气瓶标识编号
                String mzPpqpBsbh="";
                //面罩的编号
                String zmBsbh="";
                // 如果有面罩匹配的气瓶标识编号 和气瓶的标识编号相同 数量就++
                int lsNumber=0;
                //循环面面罩
                for(int k=0;k<mzNumbers.size();k++){
                    //得到面罩匹配的气瓶标识编号
                    mzPpqpBsbh=mzNumbers.get(k).respirator.qpbsh;
                    //得到面罩的标识编号
                    zmBsbh = mzNumbers.get(k).respirator.bsbh;
                    //如果气瓶的标识编号等于 面罩匹配气瓶的标识面好  证明是一套
                    if(qpBsbh.equals(mzPpqpBsbh)){
                        // 将面罩的标识编号加一起
                        tisMnBsbh=tisMnBsbh+zmBsbh+"、 ";
                        lsNumber++;
                    }
                    //如果有多个面罩匹配了相同的气瓶
                    if(lsNumber>1 && k==mzNumbers.size() -1){
                        String str=tisMnBsbh.substring(0,tisMnBsbh.length()-1);
                        tipsMsg = "气瓶标识编号为： "+qpBsbh+"的气瓶，同时被  "+str+"  面罩匹配";
                        break flag;
                    }
                }
            }






            // --------------------------开始拼装数据-----------------------------------------------------------------------------
            List<TzBean> tzBeans = new ArrayList<>();
            //这里面装的是已经被匹配的气瓶标识编号
            List<String>  yppQpsl = new ArrayList<>();
            //循环面罩列表
            for(int a=0;a<mzNumbers.size();a++){
                Sample mzObj =mzNumbers.get(a);
                TzBean tzBean = new TzBean();
                //创建excel面罩的实体
                TzMzdelBean tzMzdelBean = new TzMzdelBean();
                tzMzdelBean.setXh(a+1);




                if(mzObj.wtfIdChi!=0){ //如果二级单位不为空
                    if((mzObj.wtfChi.trim()).equals(mzObj.respirator.wtf.trim())){ //一级单位和二级单位名字一样
                        tzMzdelBean.setWtf(mzObj.respirator.wtf);
                    }else{
                        tzMzdelBean.setWtf(mzObj.respirator.wtf + mzObj.wtfChi);
                    }
                }else{
                    tzMzdelBean.setWtf(mzObj.respirator.wtf);
                }

                tzMzdelBean.setZzcs(mzObj.respirator.zzcs);
                tzMzdelBean.setXhgg(mzObj.respirator.xhgg);
                //如果选中的是减压器
                if(mzObj.respirator.jbxx_bb == 1){
                    //设置出厂编号
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(背板)");
                }else if(mzObj.respirator.jbxx_mz == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(面罩)");
                }else if(mzObj.respirator.jbxx_gqf == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(供气阀)");
                }else if(mzObj.respirator.jbxx_jyq == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(减压器)");
                }else if(mzObj.respirator.jbxx_bjq == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(报警器)");
                }else if(mzObj.respirator.jbxx_ylb == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(压力表)");
                }



                //如果设备检测是合格
                if(mzObj.sampleStatue ==4){
                    tzMzdelBean.setSfhg("合格");
                    tzMzdelBean.setBhgyy("");
                }else if(mzObj.sampleStatue == 3){ //不合格
                    tzMzdelBean.setSfhg("不合格");
                    tzMzdelBean.setBhgyy(mzObj.bhgyy);
                }else{
                    tzMzdelBean.setSfhg("");
                    tzMzdelBean.setBhgyy("");
                }
                if(mzObj.sampleStatue ==3 || mzObj.sampleStatue ==4){
                    tzMzdelBean.setBsbh(mzObj.respirator.bsbh);
                    if(null == mzObj.respirator.zsjyrq || "".equals(mzObj.respirator.zsjyrq)){
                        tzMzdelBean.setSjri("");
                    }else{

                        String msjrq =mzObj.respirator.zsjyrq;
                        String msjrq2= msjrq.replaceAll("-",".");

                        tzMzdelBean.setSjri(msjrq2);
                    }

                }else{
                    tzMzdelBean.setBsbh("");
                    tzMzdelBean.setSjri("");
                }
                //设置到组装数据中
                tzBean.setMzObj(tzMzdelBean);

                if(null != mzObj.respirator.qpbsh && !"".equals(mzObj.respirator.qpbsh)){
                    //循环气瓶的列表
                    for(Sample qpSample:qpNumbers){
                        //如果面罩的匹配气瓶信息等于 气瓶的标识编号 证明是一套
                        if(mzObj.respirator.qpbsh.equals(qpSample.bsbh)){

                            yppQpsl.add(qpSample.bsbh);
                            //创建excel 气瓶的实体
                            TzQpdelBean tzQpdelBean =new TzQpdelBean();
                            tzQpdelBean.setXh(0);
                            tzQpdelBean.setZzcs(qpSample.cylinders.zzcs);
                            tzQpdelBean.setQpxh(qpSample.cylinders.xh);
                            tzQpdelBean.setSbbh(qpSample.cylinders.sbbh);
                            tzQpdelBean.setScrq(qpSample.cylinders.scrq);

                            //如果是不合格
                            if(qpSample.sampleStatue == 3){
                                tzQpdelBean.setBhgyy(qpSample.bhgyy);
                            }else{
                                tzQpdelBean.setBhgyy("");
                            }

                            //如果不是合格 也不是不合格 证明气瓶没有报告
                            if(qpSample.sampleStatue == 3 || qpSample.sampleStatue == 4){
                                tzQpdelBean.setBsbh(qpSample.cylinders.bsbh);
                                if(null == qpSample.cylinders.zsjyrq || "".equals(qpSample.cylinders.zsjyrq)){
                                    tzQpdelBean.setSjri("");
                                }else{
                                    tzQpdelBean.setSjri(qpSample.cylinders.zsjyrq);
                                }
                            }else{
                                tzQpdelBean.setBsbh("");
                                tzQpdelBean.setSjri("");
                            }

                            //计算下次检验日期
                            // 先获取到 送检日期    3年后的前一天
                            String m_zsjyrq =qpSample.cylinders.zsjyrq;
                            if(null != m_zsjyrq && !"".equals(m_zsjyrq)){
                                String  m_zsjyrq22 = m_zsjyrq.replaceAll("-",".");
                                long year3 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(m_zsjyrq22), 3);
                                String mc_scrq = "";
                                if(qpSample.cylinders.scrq.length() == 7){
                                    mc_scrq =qpSample.cylinders.scrq+".01";
                                }else{
                                    mc_scrq = qpSample.cylinders.scrq;
                                }

                                //得到15生产日期 15年后
                                long year15 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(mc_scrq), 15);
                                //如果3年后的时间搓大于 15年后的时间搓 就使用
                                if (year3 > year15) {
                                    //使用生产日期15年后作为下次检验日期
                                    tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year15));
                                } else {
                                    //使用送检日期 三年后作为下次检验日期
                                    tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year3));
                                }
                            }else{
                                tzQpdelBean.setXcjyrq("");
                            }

                            tzBean.setQpObj(tzQpdelBean);

                        }else{
                            //tzBean.setQpObj(null);
                        }
                    }
                }
                tzBeans.add(tzBean);
            }


            //这里面装的是未匹配的气瓶标识编号
            List<Sample>  wppQpsl = new ArrayList<>();
            //在遍历一次气瓶  吧还没被匹配的找出来
            for(int p=0;p<qpNumbers.size();p++){
                Sample mqppq = qpNumbers.get(p);
                //如果已在已匹配的列表里面了
                if(yppQpsl.contains(mqppq.bsbh)){
                }else{
                    //System.out.println("包含==="+mqppq.bsbh);
                    wppQpsl.add(mqppq);
                }
            }

            //记录一下 序号到多少了
            int xhhh=tzBeans.size();
            for(int g=0;g<wppQpsl.size();g++){
                Sample xSample = wppQpsl.get(g);
                TzBean tzBean2 = new TzBean();
                tzBean2.setMzObj(null);
                //创建excel 气瓶的实体
                TzQpdelBean tzQpdelBean =new TzQpdelBean();
                tzQpdelBean.setXh(xhhh+g+1);

                if(xSample.wtfIdChi!=0){  //如果二级单位不为空
                    if((xSample.wtfChi.trim()).equals(xSample.cylinders.wtf.trim())){ //如果一级单位和二级单位名字一样
                        tzQpdelBean.setWtf(xSample.cylinders.wtf);
                    }else{
                        tzQpdelBean.setWtf(xSample.cylinders.wtf + xSample.wtfChi);
                    }
                }else{
                    tzQpdelBean.setWtf(xSample.cylinders.wtf);
                }
                tzQpdelBean.setZzcs(xSample.cylinders.zzcs);
                tzQpdelBean.setQpxh(xSample.cylinders.xh);
                tzQpdelBean.setSbbh(xSample.cylinders.sbbh);
                tzQpdelBean.setScrq(xSample.cylinders.scrq);


                //如果是不合格
                if(xSample.sampleStatue == 3){
                    tzQpdelBean.setBhgyy(xSample.bhgyy);
                }else{
                    tzQpdelBean.setBhgyy("");
                }
                if(xSample.sampleStatue == 3  || (xSample.sampleStatue == 4)){
                    tzQpdelBean.setBsbh(xSample.cylinders.bsbh);

                    if(null == xSample.cylinders.zsjyrq || "".equals(xSample.cylinders.zsjyrq)){
                        tzQpdelBean.setSjri("");
                    }else{
                        tzQpdelBean.setSjri(xSample.cylinders.zsjyrq);
                    }
                }else{
                    tzQpdelBean.setBsbh("");
                    tzQpdelBean.setSjri("");
                }
                //计算下次检验日期

                String m_zsjyrq =xSample.cylinders.zsjyrq;
                if(null != m_zsjyrq && !"".equals(m_zsjyrq)){
                    String  m_zsjyrq22 = m_zsjyrq.replaceAll("-",".");
                    // 先获取到 送检日期    3年后的前一天
                    long year3 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(m_zsjyrq22), 3);
                    String mc_scrq = "";
                    if(xSample.cylinders.scrq.length() == 7){
                        mc_scrq =xSample.cylinders.scrq+".01";
                    }else{
                        mc_scrq = xSample.cylinders.scrq;
                    }

                    //得到15生产日期 15年后
                    long year15 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(mc_scrq), 15);
                    //如果3年后的时间搓大于 15年后的时间搓 就使用
                    if (year3 > year15) {
                        //使用生产日期15年后作为下次检验日期
                        tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year15));
                    } else {
                        //使用送检日期 三年后作为下次检验日期
                        tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year3));
                    }
                }else{
                    tzQpdelBean.setXcjyrq("");
                }
                tzBean2.setQpObj(tzQpdelBean);
                if(null !=tzQpdelBean){
                    tzBeans.add(tzBean2);
                }
            }
            //遍历组合后的数据
            for(int j=0;j<tzBeans.size();j++){
                TzMzdelBean mzOb = tzBeans.get(j).getMzObj();
                TzQpdelBean qpObj = tzBeans.get(j).getQpObj();

                if(mzOb !=null ){
                    //设置 任务单编号  从0行开始的 所以要加1
                    Row row = sheet0.getRow(j+3);
                    //得到第j行的第一个格子
                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);
                    Cell cell7 = row.getCell(6);
                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);

                    if(mzOb.getXh() !=0){
                        cell1.setCellValue(mzOb.getXh());
                    }
                    cell2.setCellValue(mzOb.getWtf());
                    cell3.setCellValue(mzOb.getZzcs());
                    cell4.setCellValue(mzOb.getXhgg());

                    cell5.setCellValue(mzOb.getJyqbh());

                    cell6.setCellValue(mzOb.getSjri());
                    cell7.setCellValue(mzOb.getBsbh());
                    cell8.setCellValue(mzOb.getSfhg());
                    cell9.setCellValue(mzOb.getBhgyy());
                }

                if(null !=qpObj){
                    //设置 任务单编号  从0行开始的 所以要加1
                    Row row = sheet0.getRow(j+3);
                    //得到第j行的第一个格子
                    Cell cell1 = row.getCell(0);
                    if(qpObj.getXh() !=0){
                        cell1.setCellValue(qpObj.getXh());
                    }

                    Cell cell2 = row.getCell(1);
                    if("".equals(cell2.getStringCellValue())){
                        cell2.setCellValue(qpObj.getWtf());
                    }


                    CellStyle style = workbook.createCellStyle();
                    style.setFillForegroundColor(IndexedColors.MAROON.getIndex());


                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    //设置垂直对齐的样式为居中对齐;
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);

                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);

                    //气瓶是从11开始的
                    Cell cell10= row.getCell(9);



                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);
                    Cell cell13 = row.getCell(12);
                    Cell cell14 = row.getCell(13);
                    Cell cell15 = row.getCell(14);
                    Cell cell16 = row.getCell(15);
                    Cell cell17 = row.getCell(16);

                    cell10.setCellValue(qpObj.getZzcs());
                    cell11.setCellValue(qpObj.getQpxh());
                    cell12.setCellValue(qpObj.getSbbh());
                    cell13.setCellValue(qpObj.getScrq());
                    cell14.setCellValue(qpObj.getSjri());
                    cell15.setCellValue(qpObj.getXcjyrq());
                    cell16.setCellValue(qpObj.getBsbh());
                    if(null !=cell17){
                        cell17.setCellValue(qpObj.getBhgyy());
                    }



//                    cell10.setCellStyle(style);
//                    cell11.setCellStyle(style);
//                    cell12.setCellStyle(style);
//                    cell13.setCellStyle(style);
//                    cell14.setCellStyle(style);
//                    cell15.setCellStyle(style);
//                    cell16.setCellStyle(style);
//                    cell17.setCellStyle(style);

                }

            }


            if(!"".equals(tipsMsg)){
                resultMsg.setCode(ResultMsg.ERROR);
                resultMsg.setMsg(tipsMsg);
                return  resultMsg;
            }
            long timesss= TimeUtil.currentTimeMillis();
            String fname = TimeUtil.formatTime_6(timesss);

            String fileName = "TZ_"+fname+".xlsx";
            downLoadExcel(fileName, response, workbook);

        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        return  resultMsg;
    }




    /**
     * 台账统计的分页查询任务
     *
     * @param request
     * @return
     */
    @RequestMapping("/api/downloadTzData2")
    public ResultMsg downloadTzData2(HttpServletResponse response, HttpServletRequest request,
                                    @RequestParam(name = "startTime", required = false, defaultValue = "")  String startTime,
                                    @RequestParam(name = "endTime", required = false, defaultValue = "")  String endTime,
                                    @RequestParam(defaultValue = "-1") String wtfId,
                                    @RequestParam(defaultValue = "-1") String rwdbh,
                                    @RequestParam(defaultValue = "0") String wtfchId
    ) {
        ResultMsg resultMsg = new ResultMsg();

        try {
            long mstarttime = 0;
            long mendTime = 0;
            long mwtfId = 0;
            if (wtfId.equals(-1)) {
                mwtfId = 0;
            } else {
                mwtfId = Long.parseLong(wtfId);
            }

            if("".equals(startTime) ){
                mstarttime = 0;
            }else{
                mstarttime = TimeUtil.formatDate_5(startTime);
            }


            if("".equals(endTime)){
                mendTime = 0;
            }else{
                mendTime = TimeUtil.formatDate_5(endTime);
            }


            long mWtfchId =0;
            if("0".equals(wtfchId)){
                mWtfchId =0;
            }else{
                mWtfchId = Long.parseLong(wtfchId);
            }

            //查询的是work表
            List<WorkBean> workBeans = workMapper.getTzStatisticsPage2(rwdbh,mwtfId,mWtfchId, mstarttime, mendTime);

            //把所有的标识编号都取出来
            List<String> bsbhList = new ArrayList<>();
            for (WorkBean workBean : workBeans) {
                bsbhList.add(workBean.getBsbh());
            }
            //然后查询已完成的报告 面罩的个数
            List<Sample> mzNumbers = sampleDao.getReportByBatch(bsbhList, 1);
            //然后查询已完成的报告 气瓶的个数
            List<Sample> qpNumbers = sampleDao.getReportByBatch(bsbhList, 2);




            ClassPathResource cpr = new ClassPathResource("excleTemplate/taizhang.xlsx");
            InputStream is = cpr.getInputStream();
            Workbook workbook = new XSSFWorkbook(is);
            Sheet sheet0 = workbook.getSheetAt(0);
            //最后的提示信息
            String tipsMsg="";
            //先循环气瓶
            flag:
            for(int a=0;a<qpNumbers.size();a++){
                //气瓶标识编号
                String qpBsbh=qpNumbers.get(a).bsbh;
                //提示信息
                String tisMnBsbh="";
                //面罩匹配的气瓶标识编号
                String mzPpqpBsbh="";
                //面罩的编号
                String zmBsbh="";
                // 如果有面罩匹配的气瓶标识编号 和气瓶的标识编号相同 数量就++
                int lsNumber=0;
                //循环面面罩
                for(int k=0;k<mzNumbers.size();k++){
                    //得到面罩匹配的气瓶标识编号
                    mzPpqpBsbh=mzNumbers.get(k).respirator.qpbsh;
                    //得到面罩的标识编号
                    zmBsbh = mzNumbers.get(k).respirator.bsbh;
                    //如果气瓶的标识编号等于 面罩匹配气瓶的标识面好  证明是一套
                    if(qpBsbh.equals(mzPpqpBsbh)){
                        // 将面罩的标识编号加一起
                        tisMnBsbh=tisMnBsbh+zmBsbh+"、 ";
                        lsNumber++;
                    }
                    //如果有多个面罩匹配了相同的气瓶
                    if(lsNumber>1 && k==mzNumbers.size() -1){
                        String str=tisMnBsbh.substring(0,tisMnBsbh.length()-1);
                        tipsMsg = "气瓶标识编号为： "+qpBsbh+"的气瓶，同时被  "+str+"  面罩匹配";
                        break flag;
                    }
                }
            }






            // --------------------------开始拼装数据-----------------------------------------------------------------------------
            List<TzBean> tzBeans = new ArrayList<>();
            //这里面装的是已经被匹配的气瓶标识编号
            List<String>  yppQpsl = new ArrayList<>();
            //循环面罩列表
            for(int a=0;a<mzNumbers.size();a++){
                Sample mzObj =mzNumbers.get(a);
                TzBean tzBean = new TzBean();
                //创建excel面罩的实体
                TzMzdelBean tzMzdelBean = new TzMzdelBean();
                tzMzdelBean.setXh(a+1);




                if(mzObj.wtfIdChi!=0){ //如果二级单位不为空
                    if((mzObj.wtfChi.trim()).equals(mzObj.respirator.wtf.trim())){ //一级单位和二级单位名字一样
                        tzMzdelBean.setWtf(mzObj.respirator.wtf);
                    }else{
                        tzMzdelBean.setWtf(mzObj.respirator.wtf + mzObj.wtfChi);
                    }
                }else{
                    tzMzdelBean.setWtf(mzObj.respirator.wtf);
                }

                tzMzdelBean.setZzcs(mzObj.respirator.zzcs);
                tzMzdelBean.setXhgg(mzObj.respirator.xhgg);
                //如果选中的是减压器
                if(mzObj.respirator.jbxx_bb == 1){
                    //设置出厂编号
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(背板)");
                }else if(mzObj.respirator.jbxx_mz == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(面罩)");
                }else if(mzObj.respirator.jbxx_gqf == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(供气阀)");
                }else if(mzObj.respirator.jbxx_jyq == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(减压器)");
                }else if(mzObj.respirator.jbxx_bjq == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(报警器)");
                }else if(mzObj.respirator.jbxx_ylb == 1){
                    tzMzdelBean.setJyqbh(mzObj.respirator.ccbh+"(压力表)");
                }



                //如果设备检测是合格
                if(mzObj.sampleStatue ==4){
                    tzMzdelBean.setSfhg("合格");
                    tzMzdelBean.setBhgyy("");
                }else if(mzObj.sampleStatue == 3){ //不合格
                    tzMzdelBean.setSfhg("不合格");
                    tzMzdelBean.setBhgyy(mzObj.bhgyy);
                }else{
                    tzMzdelBean.setSfhg("");
                    tzMzdelBean.setBhgyy("");
                }
                if(mzObj.sampleStatue ==3 || mzObj.sampleStatue ==4){
                    tzMzdelBean.setBsbh(mzObj.respirator.bsbh);
                    if(null == mzObj.respirator.zsjyrq || "".equals(mzObj.respirator.zsjyrq)){
                        tzMzdelBean.setSjri("");
                    }else{

                        String msjrq =mzObj.respirator.zsjyrq;
                        String msjrq2= msjrq.replaceAll("-",".");

                        tzMzdelBean.setSjri(msjrq2);
                    }

                }else{
                    tzMzdelBean.setBsbh("");
                    tzMzdelBean.setSjri("");
                }

                String xxbh = tzMzdelBean.getBsbh();
                if(!"".equals(xxbh)){
                    String xxbh1 = xxbh.substring(0,4);
                    String xxbh2 = xxbh.substring(8);
                    String  xxbh3 = xxbh1+xxbh2;
                    long xxbh4 = Long.parseLong(xxbh3);
                    tzBean.setQpPx(xxbh4);
                }
                //设置到组装数据中
                tzBean.setMzObj(tzMzdelBean);

                if(null != mzObj.respirator.qpbsh && !"".equals(mzObj.respirator.qpbsh)){
                    //循环气瓶的列表
                    for(Sample qpSample:qpNumbers){
                        //如果面罩的匹配气瓶信息等于 气瓶的标识编号 证明是一套
                        if(mzObj.respirator.qpbsh.equals(qpSample.bsbh)){

                            yppQpsl.add(qpSample.bsbh);
                            //创建excel 气瓶的实体
                            TzQpdelBean tzQpdelBean =new TzQpdelBean();
                            tzQpdelBean.setXh(0);
                            tzQpdelBean.setZzcs(qpSample.cylinders.zzcs);
                            tzQpdelBean.setQpxh(qpSample.cylinders.xh);
                            tzQpdelBean.setSbbh(qpSample.cylinders.sbbh);
                            tzQpdelBean.setScrq(qpSample.cylinders.scrq);

                            //如果是不合格
                            if(qpSample.sampleStatue == 3){
                                tzQpdelBean.setBhgyy(qpSample.bhgyy);
                            }else{
                                tzQpdelBean.setBhgyy("");
                            }

                            //如果不是合格 也不是不合格 证明气瓶没有报告
                            if(qpSample.sampleStatue == 3 || qpSample.sampleStatue == 4){
                                tzQpdelBean.setBsbh(qpSample.cylinders.bsbh);
                                if(null == qpSample.cylinders.zsjyrq || "".equals(qpSample.cylinders.zsjyrq)){
                                    tzQpdelBean.setSjri("");
                                }else{
                                    tzQpdelBean.setSjri(qpSample.cylinders.zsjyrq);
                                }
                            }else{
                                tzQpdelBean.setBsbh("");
                                tzQpdelBean.setSjri("");
                            }

                            //计算下次检验日期
                            // 先获取到 送检日期    3年后的前一天
                            String m_zsjyrq =qpSample.cylinders.zsjyrq;
                            if(null != m_zsjyrq && !"".equals(m_zsjyrq)){
                                String  m_zsjyrq22 = m_zsjyrq.replaceAll("-",".");
                                long year3 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(m_zsjyrq22), 3);
                                String mc_scrq = "";
                                if(qpSample.cylinders.scrq.length() == 7){
                                    mc_scrq =qpSample.cylinders.scrq+".01";
                                }else{
                                    mc_scrq = qpSample.cylinders.scrq;
                                }

                                //得到15生产日期 15年后
                                long year15 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(mc_scrq), 15);
                                //如果3年后的时间搓大于 15年后的时间搓 就使用
                                if (year3 > year15) {
                                    //使用生产日期15年后作为下次检验日期
                                    tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year15));
                                } else {
                                    //使用送检日期 三年后作为下次检验日期
                                    tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year3));
                                }
                            }else{
                                tzQpdelBean.setXcjyrq("");
                            }

                            tzBean.setQpObj(tzQpdelBean);

                        }else{
                            //tzBean.setQpObj(null);
                        }
                    }
                }
                tzBeans.add(tzBean);
            }




            //这里面装的是未匹配的气瓶标识编号
            List<Sample>  wppQpsl = new ArrayList<>();
            //在遍历一次气瓶  吧还没被匹配的找出来
            for(int p=0;p<qpNumbers.size();p++){
                Sample mqppq = qpNumbers.get(p);
                //如果已在已匹配的列表里面了
                if(yppQpsl.contains(mqppq.bsbh)){
                    //System.out.println("包含==="+mqppq.bsbh);
                }else{
                    wppQpsl.add(mqppq);
                }
            }

            //记录一下 序号到多少了
            int xhhh=tzBeans.size();
            for(int g=0;g<wppQpsl.size();g++){
                Sample xSample = wppQpsl.get(g);
                TzBean tzBean2 = new TzBean();
                tzBean2.setMzObj(null);
                //创建excel 气瓶的实体
                TzQpdelBean tzQpdelBean =new TzQpdelBean();
                tzQpdelBean.setXh(xhhh+g+1);

                if(xSample.wtfIdChi!=0){  //如果二级单位不为空
                    if((xSample.wtfChi.trim()).equals(xSample.cylinders.wtf.trim())){ //如果一级单位和二级单位名字一样
                        tzQpdelBean.setWtf(xSample.cylinders.wtf);
                    }else{
                        tzQpdelBean.setWtf(xSample.cylinders.wtf + xSample.wtfChi);
                    }
                }else{
                    tzQpdelBean.setWtf(xSample.cylinders.wtf);
                }
                tzQpdelBean.setZzcs(xSample.cylinders.zzcs);
                tzQpdelBean.setQpxh(xSample.cylinders.xh);
                tzQpdelBean.setSbbh(xSample.cylinders.sbbh);
                tzQpdelBean.setScrq(xSample.cylinders.scrq);


                //如果是不合格
                if(xSample.sampleStatue == 3){
                    tzQpdelBean.setBhgyy(xSample.bhgyy);
                }else{
                    tzQpdelBean.setBhgyy("");
                }
                if(xSample.sampleStatue == 3  || (xSample.sampleStatue == 4)){
                    tzQpdelBean.setBsbh(xSample.cylinders.bsbh);

                    if(null == xSample.cylinders.zsjyrq || "".equals(xSample.cylinders.zsjyrq)){
                        tzQpdelBean.setSjri("");
                    }else{
                        tzQpdelBean.setSjri(xSample.cylinders.zsjyrq);
                    }
                }else{
                    tzQpdelBean.setBsbh("");
                    tzQpdelBean.setSjri("");
                }
                //计算下次检验日期

                String m_zsjyrq =xSample.cylinders.zsjyrq;
                if(null != m_zsjyrq && !"".equals(m_zsjyrq)){
                    String  m_zsjyrq22 = m_zsjyrq.replaceAll("-",".");
                    // 先获取到 送检日期    3年后的前一天
                    long year3 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(m_zsjyrq22), 3);
                    String mc_scrq = "";
                    if(xSample.cylinders.scrq.length() == 7){
                        mc_scrq =xSample.cylinders.scrq+".01";
                    }else{
                        mc_scrq = xSample.cylinders.scrq;
                    }

                    //得到15生产日期 15年后
                    long year15 = TimeUtil.getDateBeforeYear(TimeUtil.parsex(mc_scrq), 15);
                    //如果3年后的时间搓大于 15年后的时间搓 就使用
                    if (year3 > year15) {
                        //使用生产日期15年后作为下次检验日期
                        tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year15));
                    } else {
                        //使用送检日期 三年后作为下次检验日期
                        tzQpdelBean.setXcjyrq(TimeUtil.formatTime_tow(year3));
                    }
                }else{
                    tzQpdelBean.setXcjyrq("");
                }

                String xxbh = tzQpdelBean.bsbh;
                if(!"".equals(xxbh)){
                    String xxbh1 = xxbh.substring(0,4);
                    String xxbh2 = xxbh.substring(8);
                    String  xxbh3 = xxbh1+xxbh2;
                    long xxbh4 = Long.parseLong(xxbh3);
                    tzBean2.setQpPx(xxbh4);
                }
                tzBean2.setQpObj(tzQpdelBean);
                if(null !=tzQpdelBean){
                    tzBeans.add(tzBean2);
                }
            }
            // --------------------------结束拼装数据-----------------------------------------------------------------------------
            Collections.sort(tzBeans, new Comparator<TzBean>() {
                @Override
                public int compare(TzBean o1, TzBean o2) {
                    if ((o1.QpPx > o2.QpPx)){ //比较月份大小
                        return 1;
                    }
                    if (o1.QpPx == o2.QpPx){ //比较月份大小
                        return 0;
                    }
                    return -1;
                }
            });





            //遍历组合后的数据
            for(int j=0;j<tzBeans.size();j++){
                TzMzdelBean mzOb = tzBeans.get(j).getMzObj();
                TzQpdelBean qpObj = tzBeans.get(j).getQpObj();

                if(mzOb !=null ){
                    //设置 任务单编号  从0行开始的 所以要加1
                    Row row = sheet0.getRow(j+3);
                    //得到第j行的第一个格子
                    Cell cell1 = row.getCell(0);
                    Cell cell2 = row.getCell(1);
                    Cell cell3 = row.getCell(2);
                    Cell cell4 = row.getCell(3);
                    Cell cell5 = row.getCell(4);
                    Cell cell6 = row.getCell(5);
                    Cell cell7 = row.getCell(6);
                    Cell cell8 = row.getCell(7);
                    Cell cell9 = row.getCell(8);

                    if(mzOb.getXh() !=0){
                        cell1.setCellValue(mzOb.getXh());
                    }
                    cell2.setCellValue(mzOb.getWtf());
                    cell3.setCellValue(mzOb.getZzcs());
                    cell4.setCellValue(mzOb.getXhgg());

                    cell5.setCellValue(mzOb.getJyqbh());

                    cell6.setCellValue(mzOb.getSjri());
                    cell7.setCellValue(mzOb.getBsbh());
                    cell8.setCellValue(mzOb.getSfhg());
                    cell9.setCellValue(mzOb.getBhgyy());
                }

                if(null !=qpObj){
                    //设置 任务单编号  从0行开始的 所以要加1
                    Row row = sheet0.getRow(j+3);
                    //得到第j行的第一个格子
                    Cell cell1 = row.getCell(0);
                    if(qpObj.getXh() !=0){
                        cell1.setCellValue(qpObj.getXh());
                    }

                    Cell cell2 = row.getCell(1);
                    if("".equals(cell2.getStringCellValue())){
                        cell2.setCellValue(qpObj.getWtf());
                    }


                    CellStyle style = workbook.createCellStyle();
                    style.setFillForegroundColor(IndexedColors.MAROON.getIndex());


                    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    //设置垂直对齐的样式为居中对齐;
                    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);

                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);

                    //气瓶是从11开始的
                    Cell cell10= row.getCell(9);



                    Cell cell11 = row.getCell(10);
                    Cell cell12 = row.getCell(11);
                    Cell cell13 = row.getCell(12);
                    Cell cell14 = row.getCell(13);
                    Cell cell15 = row.getCell(14);
                    Cell cell16 = row.getCell(15);
                    Cell cell17 = row.getCell(16);

                    cell10.setCellValue(qpObj.getZzcs());
                    cell11.setCellValue(qpObj.getQpxh());
                    cell12.setCellValue(qpObj.getSbbh());
                    cell13.setCellValue(qpObj.getScrq());
                    cell14.setCellValue(qpObj.getSjri());
                    cell15.setCellValue(qpObj.getXcjyrq());
                    cell16.setCellValue(qpObj.getBsbh());
                    if(null !=cell17){
                        cell17.setCellValue(qpObj.getBhgyy());
                    }

                }

            }

            if(!"".equals(tipsMsg)){
                resultMsg.setCode(ResultMsg.ERROR);
                resultMsg.setMsg(tipsMsg);
                return  resultMsg;
            }
            long timesss= TimeUtil.currentTimeMillis();
            String fname = TimeUtil.formatTime_6(timesss);

            String fileName = "TZ_"+fname+".xlsx";
            downLoadExcel(fileName, response, workbook);

        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        return  resultMsg;
    }









    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
            workbook.write(response.getOutputStream());
            System.out.println(111111);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}
